import math
from datetime import datetime
from typing import List

from database.mysqlhelper import MySqLHelper

db = MySqLHelper()

select_str = 'select f_code,f_rq,f_kp,f_sp,f_max,f_min,f_cjl,f_cje,f_zf,f_update_time from t_stock_day_k '
insert_sql = 'insert into t_stock_day_k (f_code,f_rq,f_kp,f_sp,f_max,f_min,f_cjl,f_cje,f_zf) VALUES (%s, %s,%s,%s,%s,%s,%s,%s,%s)'


class StockDayK:
    def __init__(self):
        self.code: str = ''
        self.rq: datetime.date = ''
        self.kp = 0
        self.sp = 0
        self.max = 0
        self.min = 0
        self.cjl = 0
        self.cje = 0
        self.zf = 0
        self.up_time = ''


# 获取最新的n条
def select_n(code, size) -> List[StockDayK]:
    sql1 = select_str + ' where f_code = %s order by f_rq desc limit %s '
    return format_mul(db.selectall(sql1, (code, size)))


# 根据日期查询所有
def select_by_rq(rq):
    sql1 = select_str + ' where f_rq = %s '
    return format_mul(db.selectall(sql1, (rq)))


# 格式化结果
def format_info(res) -> StockDayK:
    if not res:
        return
    sdk = StockDayK()
    sdk.code = str(res[0], encoding="utf-8")
    sdk.rq = res[1]
    sdk.kp = float(res[2])
    sdk.sp = float(res[3])
    sdk.max = float(res[4])
    sdk.min = float(res[5])
    sdk.cjl = float(res[6])
    sdk.cje = float(res[7])
    sdk.zf = float(res[8])
    sdk.up_time = res[9]
    return sdk


# 格式化结果
def format_mul(res) -> List[StockDayK]:
    if not res:
        return []
    list = []
    for p in res:
        list.append(format_info(p))
    return list


# data {}
def insert(data):
    li = (
        data['code'], data['rq'], float(data['kp']), float(data['sp']),
        float(data['max']), float(data['min']), float(data['cjl']), float(data['cje']), float(data['zf']))
    count = db.insertone(insert_sql, li)
    return count


# 添加多条
# data = []
def insert_multiple(data_list):
    d_len = len(data_list)
    if d_len == 0:
        return 0
    # 增加多条
    sql1 = 'insert into t_stock_day_k (f_code,f_rq,f_kp,f_sp,f_max,f_min,f_cjl,f_cje,f_zf) ' \
           'VALUES (%s, %s,%s,%s,%s,%s,%s,%s,%s)'
    c_len = math.ceil(d_len / 100)
    count = 0
    for i in range(c_len):
        li = []
        start = min(i * 100, d_len)
        end = min(start + 100, d_len)
        if start == end:
            break
        for data in data_list[start:end]:
            li.append((
                data['code'], data['rq'], float(data['kp']), float(data['sp']),
                float(data['max']), float(data['min']), float(data['cjl']), float(data['cje']), float(data['zf'])))
        count += db.insertmany(sql1, li)
    return count


# 获取最新的一条
def select(code):
    sql1 = select_str + ' where f_code = %s order by f_rq desc limit 1 '
    return format_one(db.selectone(sql1, code))


# 统计条数
def select_count(code):
    sql1 = 'select count(1) from t_stock_day_k where f_code = %s '
    return db.selectone(sql1, code)[0]


# 获取最新的一条
# 日期从小到大
def select_all_asc(code):
    sql1 = select_str + 'where f_code = %s order by f_rq asc '
    return format_res(db.selectall(sql1, code))


# 获取最新的一条
# 日期从小到大
def select_all_desc(code):
    sql1 = select_str + 'where f_code = %s order by f_rq desc '
    return format_res(db.selectall(sql1, code))


# 获取最新的n条
def select_size(code, size):
    sql1 = select_str + ' where f_code = %s order by f_rq desc limit %s '
    res = db.selectall(sql1, (code, size))
    return format_res(res)


# 获取最新的n条
def select_rq_size(code, beg_rq, size):
    sql1 = select_str + ' where f_code = %s and f_rq <= %s order by f_rq desc limit %s '
    return format_res(db.selectall(sql1, (code, beg_rq, size)))


# 获取区间值
def select_interval(code, beg_rq, end_rq):
    # 获取多条
    sql1 = select_str + ' where f_code = %s and f_rq between %s and %s order by f_rq desc '
    return format_res(db.selectall(sql1, (code, beg_rq, end_rq)))


# 获取区间值
def select_interval_asc(code, beg_rq, end_rq):
    # 获取多条
    sql1 = select_str + ' where f_code = %s and f_rq between %s and %s order by f_rq '
    return format_res(db.selectall(sql1, (code, beg_rq, end_rq)))


# 获取区间值
def select_rank_zf(q_rq):
    # 获取多条
    sql1 = select_str + ' where f_rq = %s order by f_zf desc limit 1000 '
    return format_res(db.selectall(sql1, q_rq))


# 更新一条数据
def update(data):
    sql1 = 'update t_stock_day_k set f_kp = %s, f_sp = %s, f_max = %s, f_min = %s, f_cjl = %s, f_cje = %s,f_zf = %s ' \
           'where f_code = %s and f_rq = %s '
    return db.update(sql1, (
        data['kp'], data['sp'], data['max'], data['min'], data['cjl'], data['cje'], data['zf'], data['code'],
        data['rq']))


# 删除数值区间
def delete_interval(code, rq):
    sql1 = 'delete from  t_stock_day_k where f_code = %s and f_rq >= %s '
    return db.delete(sql1, (code, rq))


# 格式化结果
def format_one(res):
    if not res:
        return {}
    return {'code': str(res[0], encoding="utf-8"), 'rq': res[1], 'kp': float(res[2]), 'sp': float(res[3]),
            'max': float(res[4]), 'min': float(res[5]), 'cjl': float(res[6]), 'cje': float(res[7]),
            'zf': float(res[8]), 'up_time': res[9]}


# 格式化结果
def format_res(res):
    if not res:
        return []
    list = []
    for p in res:
        list.append(format_one(p))
    return list


if __name__ == '__main__':
    print('start')
    # count = delete_interval('000000', '2021-06-15')
    # count = select_count('000000')
    # print(count)
    r_list = select_by_rq('2021-09-02')
    print(r_list)
